﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using MySql.Data.MySqlClient;

namespace BodyTrain
{
    //MySQL数据库服务
    class MySQL_Service 
    {
        //私有参数
        private MySqlConnection mysql;  //数据库句柄
        private const string mysqlStr = "Database = sports; Data Source = 127.0.0.1; User Id = root; Password = 6333545998; SSL Mode = none; port = 3306";  //本地连接时，不加SSL Mode = none，会出现The host does not support SSL connections.报错
        //private const string mysqlStr = "Database = sports; Data Source = 192.168.23.1; User Id = root; Password = 6333545998; SSL Mode = none; port = 3306";  //本地连接时，不加SSL Mode = none，会出现The host does not support SSL connections.报错
        #region 数据库连接
        public void ConnectSer()
        {
            mysql = new MySqlConnection(mysqlStr);

            try
            {
                mysql.Open();
            }
            catch (Exception ex)
            {              
                Console.WriteLine("Open database failed!" + ex.Message);
            }
        }
        #endregion

        #region 添加平衡训练项目信息
        public void InsertBalance(String user, String relation, String item, int duration, int leftNum, int rightNum)//用户，成员，项目名，持续时间(单位秒)，左侧次数，右侧次数
        {
            String sqlinsert = "insert into balance(name,relation,item,duration,leftNum,rightNum) values('" + user + "','" + relation + "','" + item + "'," + duration.ToString()+ ","+leftNum.ToString() + "," + rightNum.ToString() + ")";    //内容格式是参考MySQL相应命令语句编写的
            MySqlCommand mySqlCommand = new MySqlCommand(sqlinsert, mysql); //创建MySQL命令
            try
            {
                mySqlCommand.ExecuteNonQuery(); //执行命令
            }
            catch (Exception ex)
            {               
                Console.WriteLine("Insert BalanceData failed!" + ex.Message);
            }
        }
        #endregion

        #region 添加儿童训练项目信息
        public void InsertChildren(String user, String relation, String item, double data)//用户，成员，项目名，数值
        {
            String sqlinsert = "insert into children(user,relation,item,data) values('" + user + "','" + relation + "','" + item + "'," + data.ToString() + ")";    //内容格式是参考MySQL相应命令语句编写的
            MySqlCommand mySqlCommand = new MySqlCommand(sqlinsert, mysql); //创建MySQL命令
            try
            {
                mySqlCommand.ExecuteNonQuery(); //执行命令
            }
            catch (Exception ex)
            {
                Console.WriteLine("Insert ChildrenData failed!" + ex.Message);
            }
        }
        #endregion

        #region 登录界面查询
        public int LogIn(String username, String pwd,String relation)//用户名，密码，亲属关系    0.三个数据都匹配  1.用户名错误或密码错误，2.成员选择错误
        {
            #region 检测用户名和密码是否已经数据库中
            String sqlsearch1 = "select name,pwd from user";
            MySqlCommand mySqlCommand1 = new MySqlCommand(sqlsearch1, mysql); //创建MySQL命令
            MySqlDataReader reader1 = mySqlCommand1.ExecuteReader();
            bool userTrue = false;  //用户名密码正确与否标志位            
            bool relTrue = false;   //成员选择正确与否标志位
            try
            {
                while (reader1.Read())   //Read()是真正的读数据，采用的是一条一条不可回的顺序读法，每执行一次Read()，reader的当前数据往后移了，所以要通过循环来读取
                {
                    if (reader1.HasRows)//HasRows表示的是从数据库读出来的数据集dataReader中是否存在数据，它的作用是用来判断数据集是否为空，执行的结果为 true/false
                    {
                        if ((username == reader1.GetString(0)) && (pwd == reader1.GetString(1)))   //用户名和密码匹配
                        {
                            userTrue = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Failed to detect username and password!" + ex.Message);
            }
            reader1.Close();

            #endregion

            #region 检验成员是否选择正确
            if (userTrue)//userTrue = true,判断成员是否选择正确
            {
                String sqlsearch2 = "select user,relation from relation";
                MySqlCommand mySqlCommand2 = new MySqlCommand(sqlsearch2, mysql); //创建MySQL命令
                MySqlDataReader reader2 = mySqlCommand2.ExecuteReader();
                try
                {
                    while (reader2.Read())   //Read()是真正的读数据，采用的是一条一条不可回的顺序读法，每执行一次Read()，reader的当前数据往后移了，所以要通过循环来读取
                    {
                        if (reader2.HasRows)//HasRows表示的是从数据库读出来的数据集dataReader中是否存在数据，它的作用是用来判断数据集是否为空，执行的结果为 true/false
                        {
                            if ((username == reader2.GetString(0)) && (relation == reader2.GetString(1)))   //成员匹配
                            {
                                relTrue = true;
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Failed to detect username and password!" + ex.Message);
                }
                reader2.Close();
            }
            #endregion

            #region 判断标志位决定返回值
            if(userTrue == false)
            {
                return 1;
            }
            else if(relTrue == false)
            {
                return 2;
            }
            else
            {
                return 0;
            }
            #endregion
        }
        #endregion

        #region 关闭数据库连接
        public void CloseServer()
        {
            try
            {
                mysql.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Close database failed!" + ex.Message);
            }
        }
        #endregion

    }
}
